/*
--| This section is commented out to prevent accidental deletion of existing stored procedure's.
--| This can be uncommented after the stored procedure is created and confirmed that another stored procedure
--| with the same name does not exists.
--|
--| Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'au_rptStudentPhotoRoster' 
)
   DROP PROCEDURE dbo.au_rptStudentPhotoRoster
GO

*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--|--------------------------------------------------------------------------------------------------------------
--|Name:			au_rptStudentPhotoRoster
--|Author:			Jamie Swindall
--|Description:		Class roster of students with their photo
--|-------------------------------------------------------------------------------------------------------------- 
--|------------- Parameters -------------------------------------------------------------------------------------
--|--------------------------------------------------------------------------------------------------------------
--| Name		||	Data Type	||	Default Value	||	Description
--|--------------------------------------------------------------------------------------------------------------
--|@ProgramId || INT || 1 || Determines the Program that the report is based on.
--|@StatusId || INT || 1 || Filters list of students by their status.  ex. Active, Graduation Requested, etc.
--|@ServiceId || INT || 1 || Filters students by their Service Id.  ex. USAF, USA, USN, IO, etc.
--|@GroupTypeId || INT || 1 || Filters students by their Group Type. ex. Division, Mix 1, Mix 2, etc.
--|@CatalogId || INT || -- || Determines the Catalog Year that the report is based on.
--|--------------------------------------------------------------------------------------------------------------
--|--------------------------- Revision Information	-------------------------------------------------------------				 
--|--------------------------------------------------------------------------------------------------------------
--|	Author:				Date			Change					Description							SCR #
--|--------------------------------------------------------------------------------------------------------------
--|	Jamie Swindall		03/21/2011	Initial version						
--|														
--|--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.au_rptStudentPhotoRoster
	@ProgramId INT = 1,
	@StatusId INT = 1, 
	@ServiceId INT = 1,
	@GroupTypeId INT = 1,
	@CatalogId INT
AS
BEGIN
	SELECT
			stu.Id AS StudentId,
			stuStatus.Description,
			fileData.Data AS DisplayImage,
			svc.Description AS Service,
			svc.Agency.Description AS Agency,
			dbo.fn_DisplayName(0,per.FirstName,per.LastName,per.MiddleName,svcRank.Abbreviation) AS DisplayName			
	FROM
			au_studentcurriculumstatus stuStatus
		JOIN au_StudentCurriculum stuCur ON
			stuStatus.Id = stuCur.StudentCurriculumStatusId
			and stuStatus.Id in (1,2)
		JOIN au_Student stu ON
			stu.Id = stuCur.StudentId
		JOIN au_PersonService perSvc ON
			stu.PersonServiceId = perSvc.Id
		JOIN au_Service svc ON
			perSvc.ServiceId = svc.Id
		JOIN au_ServiceRank svcRank ON
			perSvc.ServiceRankId = svcRank.Id
		JOIN au_Person per ON
			perSvc.PersonId = per.Id
		LEFT JOIN au_BinaryFileInfo fileInfo ON
			per.BinaryFileInfoId = fileInfo.Id
		LEFT JOIN au_BinaryFileData fileData ON
			fileInfo.BinaryFileDataId = fileData.Id
		LEFT JOIN au_ServiceAgency svcAgency ON
			perSvc.ServiceAgencyId = svcAgency.Id
		JOIN au_Program program ON
			stu.ProgramId = program.Id
		JOIN au_GroupType groupType ON
			groupType.programId = program.Id
	WHERE
		programId = (@ProgramId)
	AND
		stuStatus.Id = (@StatusId)
	AND
		svc.Id = (@ServiceId)
	AND
		groupType.GroupTypeId = (@GroupTypeId)
	AND
		au_Catalog.Id = (@CatalogId)
END
GO

/*
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.au_rptStudentPhotoRoster 1, 1, 1, 27
GO
*/